package com.ss.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.util.List;
import java.util.Map;

@Slf4j
public class ExcelToInsertSQLListener extends AnalysisEventListener<Map<Integer, String>> {
    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    private List<Map<Integer, String>> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    private MultipartFile file;
    private String savaSQLPath;
    private String dt;

    public ExcelToInsertSQLListener(MultipartFile file,String savaSQLPath,String dt){
        this.file = file;
        this.savaSQLPath = savaSQLPath;
        this.dt = dt;
    }

    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        log.info("所有数据解析完成！");
        cachedDataList.clear();
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        StringBuilder insertSQL = new StringBuilder();
        String fileName = file.getOriginalFilename().substring(0,file.getOriginalFilename().lastIndexOf("."));

        insertSQL.append("INSERT OVERWRITE TABLE sgami_prov_dfzt." + fileName + " partition(dt='" + dt + "') VALUES\n");

        for (Map<Integer, String> map : cachedDataList) {
            insertSQL.append("(");
            boolean isFirst = true;
            for (Map.Entry<Integer, String> entry : map.entrySet()) {
                if (!isFirst) {
                    insertSQL.append(",");
                }
                insertSQL.append("'").append(entry.getValue()).append("'");
                isFirst = false;
            }
            insertSQL.append("),\n");
        }

        // 移除最后一个逗号和换行符
        if (cachedDataList.size() > 0) {
            insertSQL.setLength(insertSQL.length() - 2); // 移除最后一个逗号和换行符
        }

        log.info("拼接insert sql:\n"+insertSQL);
        saveSQLAsTxt(insertSQL,savaSQLPath + fileName + ".txt");
    }

    private void saveSQLAsTxt(StringBuilder insertSQL,String filePath){
        try (BufferedWriter writer = new BufferedWriter(new FileWriter(filePath))) {
            log.info("文件存储路径："+filePath);
            writer.write(String.valueOf(insertSQL));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}
